package com.student.system.dao;

import com.student.system.entity.StudentScore;
import com.student.system.entity.Studentmessage;
import com.student.system.entity.Teachermessage;
import com.student.system.entity.Teacherregister;
import com.student.system.utils.C3P0Untils;
import com.student.system.utils.MD5Utils;
import com.student.system.vo.MyPageInfo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class TeacherDao {
    private QueryRunner queryRunner=new QueryRunner(C3P0Untils.getDataSource());
    private static final String StudentMessage_FIELD_SQL = "`id`,`name`,`studentId`,`phone`,`identity`,`sex`,`age`,`email`,`major`,`identityId`";
    private static final String TeacherMessage_FIELD_SQL = "`id`,`name`,`teacherId`,`phone`,`identity`,`sex`,`age`,`email`,`major`,`identityId`";
    private static final String StudentRegister_FIELD_SQL = "`id`,`userName`,`phone`,`password`";
    private static final String StudentScore_FIELD_SQL = "`id`,`name`,`subject`,`score`";
    private static final String TeacherMessage_FIELD_SQL2 = "`name`,`teacherId`,`phone`,`identity`,`sex`,`age`,`email`,`major`,`identityId`";
    public void save(Teacherregister teacherregister) throws SQLException {
        String sql = "INSERT into `t_register`(`userName`,`phone`,`password`) values(?,?,?)";
        queryRunner.update(sql,teacherregister.getUserName(),teacherregister.getPhone(),teacherregister.getPassword());
    }

    public void savem(Teachermessage teachermessage) throws SQLException {
        String sql="INSERT into `teacherm`(`name`,`teacherId`,`phone`,`identity`,`sex`,`age`,`email`," +
                "`major`,`identityId`) values(?,?,?,?,?,?,?,?,?)";
        queryRunner.update(sql,teachermessage.getName(),teachermessage.getTeacherId(),teachermessage.getPhone()
                ,teachermessage.getIdentity(),teachermessage.getSex(),teachermessage.getAge(),teachermessage.getEmail(),
                teachermessage.getMajor(),teachermessage.getIdentityId()
        );
    }

    public Teacherregister findByusername(String username) throws SQLException {
        String sql = "select * from `t_register` where userName=?";
        return queryRunner.query(sql,new BeanHandler<>(Teacherregister.class),username);
    }

    public List<Teachermessage> messageList() throws SQLException {
        String sql = "select * from `teacherm`";
        return queryRunner.query(sql,new BeanListHandler<>(Teachermessage.class));
    }
    public List<Teacherregister> teacherRegisterList() throws SQLException {
        String sql = "select * from `t_register`";
        return queryRunner.query(sql,new BeanListHandler<>(Teacherregister.class));
    }

    public boolean checkLoginId(String loginId) throws SQLException {
        String sql = "SELECT count(*) FROM `t_register` where userName=?";
        long count = (long) queryRunner.query(sql, new ScalarHandler(), loginId);
        if (count > 0) {
            return true;
        } else {
            return false;
        }
    }

    public Teacherregister selectTeacherRegisterById(int id) throws SQLException {
        String sql = "select"+StudentRegister_FIELD_SQL+" from `t_register` where id=?";
        return queryRunner.query(sql,new BeanHandler<>(Teacherregister.class),id);
    }

    public void doEditTeacherRegister(Teacherregister teacherregister) throws SQLException {
        String sql="UPDATE `t_register` set `phone`=?,`password`=? where id=?";
        queryRunner.update(sql,teacherregister.getPhone(), MD5Utils.md5plus(teacherregister.getPhone()),teacherregister.getId());
    }

    public void doEditTeacherMessage(Teachermessage teachermessage) throws SQLException {
        String sql="UPDATE `teacherm` set `teacherId`=?,`phone`=?,`identity`=?,`email`=? where id=?";
        queryRunner.update(sql,teachermessage.getTeacherId(),teachermessage.getPhone(),teachermessage.getIdentity(),teachermessage.getEmail(),teachermessage.getId());
    }

    public Teachermessage selectTeacherMessageById(int id) throws SQLException {
        String sql = "select"+TeacherMessage_FIELD_SQL+" from `studentm` where id=?";
        return queryRunner.query(sql,new BeanHandler<>(Teachermessage.class),id);
    }

    public List<Studentmessage> list(String search) throws SQLException {
        String sql = "select"+StudentMessage_FIELD_SQL+" from `studentm`";
        Object[] params = new Object[]{};
        if(search !=null && search.trim().length()>0){
            sql+="where name like ? or phone like ? or identity like ?";
            params =new Object[]{"%"+search+"%","%"+search+"%","%"+search+"%"};
        }

        return queryRunner.query(sql,new BeanListHandler<>(Studentmessage.class),params);
    }

    /***
     * 分页查询参数
     * @param myPageInfo
     * @return
     */
    public List<Studentmessage> listByPage(MyPageInfo myPageInfo) throws SQLException {
        String search = myPageInfo.getSearch();
        String sql = "select " + StudentMessage_FIELD_SQL + " from `studentm`";
        Object[] params = new Object[]{};
        if (search != null && search.trim().length() > 0) {
            sql += "  where user_name like ? or phone like ? or city like ?";
            params = new Object[]{"%" + search + "%", "%" + search + "%", "%" + search + "%"};
        }
        int index = (myPageInfo.getPageNum() - 1) * myPageInfo.getPageSize();
        sql += " limit " + index + "," + myPageInfo.getPageSize();
        return queryRunner.query(sql, new BeanListHandler<>(Studentmessage.class), params);
    }
    public Teachermessage myMessage(String userName) throws SQLException {
        String sql = "select"+TeacherMessage_FIELD_SQL+" from `teacherm` where teacherId=?";
        return queryRunner.query(sql,new BeanHandler<>(Teachermessage.class),userName);
    }

    public int savemyMessage(String userName) throws SQLException {
       String sql = "INSERT into `teacher_message` SELECT * from `teacherm` where teacherId=?";
       return queryRunner.update(sql,new BeanHandler<>(Teachermessage.class),userName);
    }

    public List<Teachermessage> teacherMessageList() throws SQLException {
        String sql = "select"+TeacherMessage_FIELD_SQL+" from `teacherm`";
        return queryRunner.query(sql,new BeanListHandler<>(Teachermessage.class));
    }

    public List<Teachermessage> MyMessageList() throws SQLException {
        String sql = "select"+TeacherMessage_FIELD_SQL+" from `teacher_message`";
        return queryRunner.query(sql,new BeanListHandler<>(Teachermessage.class));
    }

    public StudentScore selectScoreById(int id) throws SQLException {
        String sql = "select"+StudentScore_FIELD_SQL+" from `student_score` where id=?";
        return queryRunner.query(sql,new BeanHandler<>(StudentScore.class),id);
    }

    public void doEditStudentScoreMessage(StudentScore studentScore) throws SQLException {
        String sql="UPDATE `student_score` set `subject`=?,`score`=? where id=?";
        queryRunner.update(sql,studentScore.getSubject(),studentScore.getScore(),studentScore.getId());
    }

    public List<StudentScore> studentScoreList() throws SQLException {
        String sql = "select"+StudentScore_FIELD_SQL+" from `student_score`";
        return queryRunner.query(sql,new BeanListHandler<>(StudentScore.class));
    }

    public void deleteStudentMessage(Integer id) throws SQLException {
        String sql = "delete from `studentm` where id=?";
        queryRunner.update(sql,id);
    }

    /***
     * 根据查询条件获得总记录数
     * @param myPageInfo
     * @return
     * @throws SQLException
     */
    public Long count(MyPageInfo myPageInfo) throws SQLException {
        String sql="SELECT count(*) FROM `studentm`";
        Object[] params = new Object[]{};
        if(myPageInfo.getSearch()!=null && myPageInfo.getSearch().trim().length()>0){
            sql+="where name like ? or phone like ? or identity like ?";
            params =new Object[]{"%"+myPageInfo.getSearch()+"%","%"+myPageInfo.getSearch()+"%","%"+myPageInfo.getSearch()+"%"};
        }
        return  (long) queryRunner.query(sql,new ScalarHandler(),params);

    }


}


